package com.sp.scmdb.serviceImpl;

import java.io.File;
import java.io.FileInputStream;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sp.scmdb.bean.AlarmBean;
import com.sp.scmdb.bean.EmpMonthReportBean;
import com.sp.scmdb.bean.EmpReportBean;
import com.sp.scmdb.bean.EmployeeBean;
import com.sp.scmdb.bean.LabelIOBean;
import com.sp.scmdb.bean.ReaderBean;
import com.sp.scmdb.bean.UserBean;
import com.sp.scmdb.common.CommonParamter;
import com.sp.scmdb.entity.Alarm;
import com.sp.scmdb.entity.LabelReadingMachine;
import com.sp.scmdb.entity.SuperLabelIO;
import com.sp.scmdb.entity.Tag;
import com.sp.scmdb.exception.ExcelException;
import com.sp.scmdb.service.EmployeeService;
import com.sp.scmdb.service.ExportExcelService;
import com.sp.scmdb.service.TagService;
import com.sp.scmdb.utils.EmpCollectionGroup;

@Service("exportExcelService")
public class ExportExcelServiceImpl implements ExportExcelService {

	private EmployeeService employeeService;
	private TagService tagService;

	@Autowired
	public void setTagService(TagService tagService) {
		this.tagService = tagService;
	}

	@Autowired
	public void setEmployeeService(EmployeeService employeeService) {
		this.employeeService = employeeService;
	}

	@SuppressWarnings("unchecked")
	public HSSFWorkbook createLabelIOExcel(LabelIOBean labelIOBean) throws ExcelException {
		String[][] labelIOExcelHeader = { { "姓名", "String" }, { "卡号", "String" },{ "读卡器编号", "String" },{ "读卡器名称", "String" },
				{ "进入时间", "DateTime" }, { "离开时间", "DateTime" } };
		String tableName = "轨迹信息表";
		if(StringUtils.isEmpty(labelIOBean.getInTime()))
			labelIOBean.setInTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
		StringBuilder labelIOHeaderDetail = new StringBuilder("");
		labelIOHeaderDetail.append("卡号:" + labelIOBean.getLabelID()).append(" 姓名:" + labelIOBean.getEmpName()).append(
				" 班组:" + labelIOBean.getDepID()).append(
				" 时间:" + labelIOBean.getInTime().substring(0,10) + "~" + labelIOBean.getOutTime());
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			HSSFFont font = workBook.createFont();
			HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			font.setFontHeightInPoints((short) 20);
			cellStyle.setFont(font);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, labelIOExcelHeader.length - 1));
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(tableName);
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, labelIOExcelHeader.length - 1));
			row = (HSSFRow) sheet.createRow(1);
			cell = row.createCell(0);
			cell.setCellValue(labelIOHeaderDetail.toString());
			row = (HSSFRow) sheet.createRow(2);
			for (int head = 0; head < labelIOExcelHeader.length; head++) {
				cell = row.createCell(head);
				cell.setCellValue(labelIOExcelHeader[head][0]);
			}
			List<SuperLabelIO> lists = labelIOBean.getAllList();
			if (CollectionUtils.isNotEmpty(lists)) {
				for (int i = 0; i < lists.size(); i++) {
					SuperLabelIO labelIO = lists.get(i);
					row = (HSSFRow) sheet.createRow(i + 3);
					cell = row.createCell(0);
					cell.setCellValue(labelIO.getEmployee().getEmpName());
					cell = row.createCell(1);
					cell.setCellValue(String.valueOf(labelIO.getLabelID()));
					cell = row.createCell(2);
					cell.setCellValue(String.valueOf(labelIO.getLrmID()));
					cell = row.createCell(3);
					cell.setCellValue(String.valueOf(labelIO.getRmk()));
					cell = row.createCell(4);
					cell.setCellValue(labelIO.getInTime() == null ? "" : labelIO.getInTime().toString(
							"yyyy-MM-dd HH:mm:ss"));
					cell = row.createCell(5);
					cell.setCellValue(labelIO.getOutTime() == null ? "" : labelIO.getOutTime().toString(
							"yyyy-MM-dd HH:mm:ss"));
					sheet.autoSizeColumn(i);
				}

			}

		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}

	@SuppressWarnings("unchecked")
	public HSSFWorkbook createDayReportExcel(EmpReportBean empReportBean, String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "卡号", "String" }, { "姓名", "String" }, { "部门", "String" },
					{ "进园时长", "String" }, { "进入时间 ", "DateTime" }, { "离开时间", "DateTime" },{ "班次", "String" }, { "工", "String" } };
			HSSFFont font = workBook.createFont();
			HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			font.setFontHeightInPoints((short) 20);
			cellStyle.setFont(font);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dayExcelHeader.length - 1));
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(tableName));
			row = (HSSFRow) sheet.createRow(1);
			for (int head = 0; head < dayExcelHeader.length; head++) {
				cell = row.createCell(head);
				cell.setCellValue(dayExcelHeader[head][0]);
			}
			List<EmpReportBean> lists = empReportBean.getAllList();
			if (CollectionUtils.isNotEmpty(lists)) {
				for (int i = 0; i < lists.size(); i++) {
					EmpReportBean empDayReport = lists.get(i);
					row = (HSSFRow) sheet.createRow(i + 2);
					cell = row.createCell(0);
					cell.setCellValue(String.valueOf(empDayReport.getLabelID()));
					cell = row.createCell(1);
					cell.setCellValue(empDayReport.getEmpName());
					cell = row.createCell(2);
					cell.setCellValue(empDayReport.getDepName());
					cell = row.createCell(3);
					cell.setCellValue(empDayReport.getRealTime());
					for (Map.Entry<DateTime, DateTime> map : empDayReport.getDataMap().entrySet()) {
						cell = row.createCell(4);
						cell.setCellValue(map.getKey().toString("yyyy-MM-dd HH:mm:ss"));
						cell = row.createCell(5);
						cell.setCellValue(map.getValue().toString("yyyy-MM-dd HH:mm:ss"));
					}
					cell = row.createCell(6);
					cell.setCellValue(empDayReport.getShiftName());
					cell = row.createCell(7);
					cell.setCellValue(empDayReport.getGong());
					sheet.autoSizeColumn(i);
				}
			}
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}

	public HSSFWorkbook createMonthReportExcel(EmpReportBean empReportBean, Map<Integer, String> dayOfMonth,
			String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "卡号", "String" }, { "姓名", "String" }, { "部门", "String" },
					{ "合计", "String" } };
			HSSFFont font = workBook.createFont();
			HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			font.setFontHeightInPoints((short) 20);
			cellStyle.setFont(font);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dayExcelHeader.length - 1 + dayOfMonth.size()));
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(tableName));
			row = (HSSFRow) sheet.createRow(1);
			int days = 0;
			for (int head = 0; head < dayExcelHeader.length; head++) {
				if ((head + 1) >= dayExcelHeader.length) {
					for (Map.Entry<Integer, String> day : dayOfMonth.entrySet()) {
						cell = row.createCell((head + days));
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(Integer.parseInt(day.getValue()));
						days++;
					}
				}
				cell = row.createCell(head + days);
				cell.setCellValue(dayExcelHeader[head][0]);
			}
			List<EmpMonthReportBean> lists = empReportBean.getAllList();
			if (CollectionUtils.isNotEmpty(lists)) {
				for (int i = 0; i < lists.size(); i++) {
					EmpMonthReportBean empMontReport = lists.get(i);
					int currentCell = 0;
					row = (HSSFRow) sheet.createRow(i + 2);
					cell = row.createCell(0);
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(Integer.parseInt(empMontReport.getLabelID()));
					cell = row.createCell(1);
					cell.setCellValue(empMontReport.getEmpName());
					cell = row.createCell(2);
					cell.setCellValue(empMontReport.getDepName());
					for (Map.Entry<Integer, String> day : dayOfMonth.entrySet()) {
						currentCell++;
						cell = row.createCell(currentCell + 2);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						if (empMontReport.getMonthMap().get(day.getKey()) != null) {
							cell.setCellValue(empMontReport.getMonthMap().get(day.getKey()));
							;
						} else {
							cell.setCellValue(0);
						}
					}
					currentCell++;
					cell = row.createCell(currentCell + 2);
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(empMontReport.getTotalWorkNum());
				}
			}
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}
	public HSSFWorkbook createReaderExcel(ReaderBean readerBean, String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "读卡器编号", "String" }, { "读卡器位置", "String" }, { "备注", "String" },{ "更新时间", "String" }};
			HSSFFont font = workBook.createFont();
			HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
//			row = (HSSFRow) sheet.createRow(0);
//			font.setFontHeightInPoints((short) 20);
//			cellStyle.setFont(font);
//			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dayExcelHeader.length - 1));
//			cell = row.createCell(0);
//			cell.setCellStyle(cellStyle);
//			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//			cell.setCellValue(new HSSFRichTextString(tableName));
//			row = (HSSFRow) sheet.createRow(1);
//			for (int head = 0; head < dayExcelHeader.length; head++) {
//				cell = row.createCell(head);
//				cell.setCellValue(dayExcelHeader[head][0]);
//			}
			List<LabelReadingMachine> lists = readerBean.getAllList();
			if (CollectionUtils.isNotEmpty(lists)) {
				for (int i = 0; i < lists.size(); i++) {
					LabelReadingMachine reader = new LabelReadingMachine();
					reader  = lists.get(i);
					row = (HSSFRow) sheet.createRow(i);
					cell = row.createCell(0);
					cell.setCellValue(reader.getLrmID());
					cell = row.createCell(1);
					if(reader.getLrmName()!=null)
						cell.setCellValue(reader.getLrmName());
					else
						cell.setCellValue("");
					cell = row.createCell(2);
					if(reader.getRmk()!=null)
						cell.setCellValue(reader.getRmk());
					else
						cell.setCellValue("");
					cell = row.createCell(3);
					if (reader.getLrmTime() == null)
						cell.setCellValue("");
					else
						cell.setCellValue(reader.getLrmTime().toString("yyyy-MM-dd HH:mm:ss"));
					sheet.autoSizeColumn(i);
				}
			}
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}
	public HSSFWorkbook createAlarmReportExcel(AlarmBean alarmBean, String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "报警类型", "String" }, { "姓名", "String" }, { "部门", "String" },
					{ "读卡器", "String" }, { "报警时间", "DateTime" }, { "确认时间", "DateTime" },{ "备注", "String" }, { "是否确认", "String" } };
			HSSFFont font = workBook.createFont();
			HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			font.setFontHeightInPoints((short) 20);
			cellStyle.setFont(font);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dayExcelHeader.length - 1));
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(tableName));
			row = (HSSFRow) sheet.createRow(1);
			for (int head = 0; head < dayExcelHeader.length; head++) {
				cell = row.createCell(head);
				cell.setCellValue(dayExcelHeader[head][0]);
			}
			List<AlarmBean> lists = alarmBean.getAllList();
			if (CollectionUtils.isNotEmpty(lists)) {
				Alarm alarm = new Alarm();
				for (int i = 0; i < lists.size(); i++) {
					alarmBean = lists.get(i);
					row = (HSSFRow) sheet.createRow(i + 2);
					cell = row.createCell(0);
					cell.setCellValue(alarmBean.getAlarmType());
					cell = row.createCell(1);
					if(alarmBean.getEmpName()!=null)
						cell.setCellValue(alarmBean.getEmpName());
					else
						cell.setCellValue("");
					cell = row.createCell(2);
					if(alarmBean.getDepName()!=null)
						cell.setCellValue(alarmBean.getDepName());
					else
						cell.setCellValue("");
					cell = row.createCell(3);
					if(alarmBean.getAreaName()!=null)
						cell.setCellValue(alarmBean.getAreaName());
					else
						cell.setCellValue("");
					cell = row.createCell(4);
					cell.setCellValue(alarmBean.getInTime().toString("yyyy-MM-dd HH:mm:ss"));
					cell = row.createCell(5);
					if (alarmBean.getOutTime() == null)
						cell.setCellValue("");
					else
						cell.setCellValue(alarmBean.getOutTime().toString("yyyy-MM-dd HH:mm:ss"));
					cell = row.createCell(6);
					cell.setCellValue(alarmBean.getRmk());
					cell = row.createCell(7);
					if (alarmBean.getState() == 0) {
						cell.setCellValue("未确认");
					} else {
						cell.setCellValue("已确认");
					}
					sheet.autoSizeColumn(i);
				}
			}
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}

	public HSSFWorkbook createEmployeeExcel(String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "卡号", "String" }, { "编号", "String" }, { "姓名", "String" },{ "身份证", "String" },
					{ "性别", "String" }, { "部门", "String" }, { "类型", "String" }
					 };
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			for (int head = 0; head < dayExcelHeader.length; head++) {
				cell = row.createCell(head);
				cell.setCellValue(dayExcelHeader[head][0]);
			}
			row = (HSSFRow) sheet.createRow(1);
			cell = row.createCell(0);
			cell.setCellValue("1001");
			cell = row.createCell(1);
			cell.setCellValue("1234567890");
			cell = row.createCell(2);
			cell.setCellValue("张三");
			cell = row.createCell(3);
			cell.setCellValue("123456789123456789");
			cell = row.createCell(4);
			cell.setCellValue("男");
			cell = row.createCell(5);
			cell.setCellValue("客服部");
			cell = row.createCell(6);
			cell.setCellValue("员工");
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}

	@Transactional
	public String importExcel(File file, String fileName, EmpCollectionGroup empGroup, UserBean user) {
		String errorInfo = "";
		try {
			Workbook workbook = new HSSFWorkbook(new FileInputStream(file));
			Sheet sheet = workbook.getSheetAt(0);
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				EmployeeBean employeeBean = new EmployeeBean();
				Row row = sheet.getRow(i);
				Cell cell = row.getCell(0);
				String labelID = "";
				if (cell != null) {
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						labelID = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "##");
					} else {
						labelID = cell.getStringCellValue().trim();
					}
					if (StringUtils.isNotEmpty(labelID)) {
						employeeBean.setEmpLabelID(labelID);
					}
				} else {
					errorInfo = "行" + i + ",卡号 不可以是空值";
					break;
				}
				cell = row.getCell(1);
				String workID = "";
				if (cell != null) {
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						workID = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "##");
					} else {
						workID = cell.getStringCellValue().trim();
					}
				}else {
					errorInfo = "行" + i + ",编号 不可以是空值";
					break;
				}
				employeeBean.setEmpWorkID(workID);
				cell = row.getCell(2);
				if (cell != null) {
					String name = "";
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						name = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "##");
					} else {
						name = cell.getStringCellValue().trim();
					}
					if (StringUtils.isNotEmpty(name)) {
						employeeBean.setEmpName(name);
					}
				} else {
					errorInfo = "行" + i + ",姓名不可以是空值";
					break;
				}
				cell = row.getCell(3);
				if (cell != null) {
					String identityNo = "";
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						identityNo = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "##");
					} else {
						identityNo = cell.getStringCellValue().trim();
					}
					if (StringUtils.isNotEmpty(identityNo)) {
						employeeBean.setIdentityNo(identityNo);
					}
				}
				
				cell = row.getCell(4);
				if (cell != null) {
					Map<String, String> sexMap = CommonParamter.TypeCommon.getSexType();
					for (String key : sexMap.keySet()) {
						if (sexMap.get(key).equals(cell.getStringCellValue().trim())) {
							employeeBean.setEmpSex(key);
						}
					}
				}
				cell = row.getCell(5);
				if (MapUtils.isNotEmpty(empGroup.getDepMap()) && cell != null) {
					if (empGroup.getDepMap().get(cell.getStringCellValue().trim()) == null) {
						errorInfo = "行" + i + ",部门 ：" + cell.getStringCellValue().trim() + ",不存在";
						break;
					} else {
						employeeBean.setEmpDepID(String.valueOf(empGroup.getDepMap().get(
								cell.getStringCellValue().trim()).getId()));
					}
				}
				cell = row.getCell(6);
				if (MapUtils.isNotEmpty(empGroup.getWorkTypeMap()) && cell != null) {
					if (empGroup.getWorkTypeMap().get(cell.getStringCellValue().trim()) == null) {
						errorInfo = "行" + i + ",类型 ：" + cell.getStringCellValue().trim() + ",不存在";
						break;
					} else {
						employeeBean.setEmpWorkTypeID(String.valueOf(empGroup.getWorkTypeMap().get(
								cell.getStringCellValue().trim()).getId()));
					}
				}
				
				if (StringUtils.isEmpty(errorInfo)) {
					String result = employeeService.newEmp(employeeBean, user);
					if (result.equals("10")) {
						errorInfo = "行" + i + ",卡号重复";
					} else if (result.equals("20")) {
						errorInfo = "行" + i + ",卡号已使用或者卡号不存在";
					} else if (result.equals("30")) {
						errorInfo = "行" + i + ", 编号重复";
					}
					if (StringUtils.isNotEmpty(errorInfo)) {
						break;
					}
				}
			}
			return errorInfo;
		} catch (Exception e) {
			throw new IllegalStateException();
		}
	}

	public String importTagExcel(File file, String fileName, UserBean user) {
		String errorInfo = "";
		try {
			Workbook workbook = new HSSFWorkbook(new FileInputStream(file));
			Sheet sheet = workbook.getSheetAt(0);
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Tag tag = new Tag();
				Row row = sheet.getRow(i);
				Cell cell = row.getCell(0);
				String labelID = "";
				if (cell != null) {
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						labelID = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "##");
					} else {
						labelID = cell.getStringCellValue().trim();
					}
					if (StringUtils.isNotEmpty(labelID)) {
						tag.setTagno(labelID);
					}
				} else {
					errorInfo = "行" + i + ",卡号 不可以是空值";
					break;
				}
				cell = row.getCell(1);
				if (cell != null) {
					Map<String, String> useMap = CommonParamter.TypeCommon.getUseType();
					for (String key : useMap.keySet()) {
						if (useMap.get(key).equals(cell.getStringCellValue().trim())) {
							tag.setUsestat(key);
						}
					}
					if (StringUtils.isEmpty(tag.getUsestat())) {
						errorInfo = "行" + i + ",使用状态不存在";
					}
				} else {
					// 如果表格中使用状态为空，则给默认值
					tag.setUsestat("0");
				}
				cell = row.getCell(2);
				if (cell != null) {
					Map<String, String> tagStateMap = CommonParamter.TypeCommon.getTagState();
					for (String key : tagStateMap.keySet()) {
						if (tagStateMap.get(key).equals(cell.getStringCellValue().trim())) {
							tag.setPowerstat(key);
						}
					}
					if (StringUtils.isEmpty(tag.getPowerstat())) {
						errorInfo = "行" + i + ",电量状态不存在";
					}
				} else {
					// 如果表格中电量状态为空，则给默认值
					tag.setPowerstat("0");
				}
				if (StringUtils.isEmpty(errorInfo)) {
					String result = tagService.addTag(tag, user);
					if (result.equals(CommonParamter.CHECK_REPEAT)) {
						errorInfo = "行" + i + ",卡号已存在";
					}
					if (StringUtils.isNotEmpty(errorInfo)) {
						break;
					}
				}
			}
			return errorInfo;
		} catch (Exception e) {
			throw new IllegalStateException();
		}
	}

	public HSSFWorkbook createTagExcel(String tableName) throws ExcelException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		try {
			String[][] dayExcelHeader = { { "卡号", "String" }, { "使用状态", "String" }, { "电量状态", "String" } };
			HSSFRow row = null;
			HSSFCell cell = null;
			Sheet sheet = workBook.createSheet(tableName);
			row = (HSSFRow) sheet.createRow(0);
			for (int head = 0; head < dayExcelHeader.length; head++) {
				cell = row.createCell(head);
				cell.setCellValue(dayExcelHeader[head][0]);
			}
			row = (HSSFRow) sheet.createRow(1);
			cell = row.createCell(0);
			cell.setCellValue("1001");
			cell = row.createCell(1);
			cell.setCellValue("未使用");
			cell = row.createCell(2);
			cell.setCellValue("电量正常");
		} catch (Exception e) {
			throw new ExcelException(e.getMessage());
		}
		return workBook;
	}

}
